package com.kcyu.sfherp.mapper.ms;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.kcyu.sfherp.entity.CustomHistoryPrice;
import com.kcyu.sfherp.entity.Good;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface GoodMapper extends BaseMapper<Good>{

    @Select("select sp.spbh as 'spbh',sp.spname as 'spname',kc0 as 'mskc',kc1 as 'ckkc',kc5 as 'yykc',kc2 as 'dckc',kc7 as 'zckc',newjj as 'newjj' from sp left outer join spkc on sp.spbh=spkc.spbh where spname like '%${spname}%'")
    List<Good> selectGoodsByName(@Param("spname") String spname);

    @Select("select top 100 spbh,khname,spname,prnjs,prnxs,jg,jj,je,rq from dbo.chmx where spbh like '%${spbh}%' order by rq desc")
    List<CustomHistoryPrice> queryRecentlyPricesByspbh(@Param("spbh") String spbh);

    @Select("select top 30\n" +
            "    sp.spbh,sp.spname,k as 'gg',sp.jg,newjj,kc0 as 'mskc',kc1 as 'ckkc',kc5 as 'yykc',kc2 as 'dckc',kc7 as 'zckc'\n" +
            "from sp\n" +
            "    inner join spkc on sp.spbh=spkc.spbh\n" +
            "where sp.spname like '%${spinfo}%' or sp.spbh like '%${spinfo}%'")
    List<Good> getGoodsNumber(@Param("spinfo") String spinfo);

    @Select("select top 1000 * from  ( select spbh, spname,\n" +
            "isnull((select Top 1 Kc0 from spkc where spbh=sp.spbh ),0) as mskc, \n" +
            "isnull((select Top 1 Kc0 from spkc where spbh=sp.spbh ),0)-isnull((select sum(sl) from orchmx where spbh=sp.spbh and ddbh='0' ),0) as msky, \n" +
            "isnull((select Top 1 Kc1 from spkc where spbh=sp.spbh ),0) as ckkc, \n" +
            "isnull((select Top 1 Kc1 from spkc where spbh=sp.spbh ),0)-isnull((select sum(sl) from orchmx where spbh=sp.spbh and ddbh='1' ),0) as ckky, kc,\n" +
            "(select Top 1 kc5 from spkc where spbh=sp.spbh) as yykc,\n" +
            "(select Top 1 kc2 from spkc where spbh=sp.spbh) as dckc,\n" +
            "(select Top 1 kc7 from spkc where spbh=sp.spbh) as zckc,\n" +
            "netweight, dw, jg, newjj, dw2, jskc, xskc, jspd, xspd, k as gg, p, jk, cother1,\n" +
            "cother2, cother3, cother4, cother5, nother5, stop\n" +
            "from sp where 1=1 ) sp where \n" +
            "spname like '%${spinfo}%' or spbh = '${spinfo}'\n" +
            " and stop<>'√' \n" +
            " order by spbh")
    List<Good> UnionQueryGoodsStock(@Param("spinfo") String spinfo);

    @Select("select spkc.spbh,spname,kc0 as mskc,(kc0 - sum(sl)) as msky from spkc inner join orchmx on spkc.spbh=orchmx.spbh where spkc.spbh='${spbh}' and orchmx.ddbh=0 group by spkc.spbh,spname,kc0;")
    Good GetMSStockByNumber(@Param("spbh") String spbh);

    @Select("select spkc.spbh,spname,kc1 as ckkc,(kc1 - sum(sl)) as ckky from spkc inner join orchmx on spkc.spbh=orchmx.spbh where spkc.spbh='${spbh}' and orchmx.ddbh=1 group by spkc.spbh,spname,kc1;")
    Good GetCKStockByNumber(@Param("spbh") String spbh);
}
